<?php
set_time_limit(0);
date_default_timezone_set('America/Bogota');
setlocale(LC_MONETARY, 'en_US');
require_once "../funciones.php";

$page = $_REQUEST['page']; // get the requested page
$limit = $_REQUEST['rows']; // get how many rows we want to have into the grid
$sidx = $_REQUEST['sidx']; // get index row - i.e. user click to sort
$sord = $_REQUEST['sord']; // get the direction
if(!$sidx) $sidx =1;
$con = con_caweb();
autenticado();


$wh = "";
@$searchOn = Strip($_REQUEST['_search']);
if($searchOn=='true') {
	$fld = Strip($_REQUEST['searchField']);
		$fldata = Strip($_REQUEST['searchString']);
		$foper = Strip($_REQUEST['searchOper']);
		$wh .= " AND ".$fld;
		switch ($foper) {
			case "eq":
				if(is_numeric($fldata)) {
					$wh .= " = ".$fldata;
				} else {
					$wh .= " = '".$fldata."'";
				}
				break;
			case "ne":
				if(is_numeric($fldata)) {
					$wh .= " <> ".$fldata;
				} else {
					$wh .= " <> '".$fldata."'";
				}
				break;
			case "lt":
				if(is_numeric($fldata)) {
					$wh .= " < ".$fldata;
				} else {
					$wh .= " < '".$fldata."'";
				}
				break;
			case "le":
				if(is_numeric($fldata)) {
					$wh .= " <= ".$fldata;
				} else {
					$wh .= " <= '".$fldata."'";
				}
				break;
			case "gt":
				if(is_numeric($fldata)) {
					$wh .= " > ".$fldata;
				} else {
					$wh .= " > '".$fldata."'";
				}
				break;
			case "ge":
				if(is_numeric($fldata)) {
					$wh .= " >= ".$fldata;
				} else {
					$wh .= " >= '".$fldata."'";
				}
				break;
			case "bw":
				$fldata .= "%";
				$wh .= " LIKE '".$fldata."'";
				break;
			case "bn":
				$fldata .= "%";
				$wh .= " NOT LIKE '".$fldata."'";
				break;
			case "in":
				$wh .= " in (".$fldata.")";
				break;
			case "ni":
				$wh .= " NOT IN (".$fldata.")";
				break;
			case "ew":
				$wh .= " LIKE '%".$fldata."'";
				break;
			case "en":
				$wh .= " NOT LIKE '%".$fldata."'";
				break;
			case "cn":
				$wh .= " LIKE '%".$fldata."%'";
				break;
			case "cn":
				$wh .= " NOT LIKE '%".$fldata."%'";
				break;
			default :
				$wh = "";
		}
}

    $fechaCorte = ($_REQUEST['fechaCorte'] == "")? date("Y-m-d") : $_REQUEST['fechaCorte'];
    $baseDatos = $_SESSION['caweb']['basededatos'];
    $soporte  = $_SESSION['caweb']['soporte'];

    $sucursal = "";
    if($_SESSION['caweb']['multisucursal'] == "Si"){
        $sql = "select codigosucursal from sucursales order by codigosucursal";
        $result = mysql_query($sql) or die("Error Sql3 ".  mysql_error());
        while ($row1 = mysql_fetch_array($result)) {
            $sucursal .= ",'".$row1['codigosucursal']."'";
        }
        $sucursal = substr($sucursal, 1);
    }
    else
        $sucursal = "'".$_SESSION['caweb']['sucursal']."'";
    
    $dias = $_REQUEST['dias'];

    //se construye la consulta principal
    $sql = "select  vfac.cliente as cliente,man.name as name ";
    $sql .= "from $baseDatos.vfacturables vfac ";
    $sql .= "inner join $baseDatos.vclientesPorSucursal cli on cli.cliente = vfac.cliente ";
    $sql .= "inner join clientes man on man.nit = cli.cliente ";
    $sql .= "where cli.sucursal in ($sucursal) ";
    $sql .= "and (datediff('$fechaCorte',vfac.fechafacturar) >= $dias) ".$wh." group by vfac.cliente ";
    $clientes = mysql_query($sql) or die("Error SQL1 ".  mysql_error());
    $count = mysql_numrows($clientes);

    if( $count >0 )
            $total_pages = ceil($count/$limit);
    else
            $total_pages = 0;
    //valida el limite de la consulta segun el numero de registros
    if ($page > $total_pages) $page=$total_pages;
            $start = $limit*$page - $limit; 
    if ($start<0) $start = 0;

    //se construye la consulta principal
    $sql .= " ORDER BY $sidx $sord LIMIT $start , $limit";
    //echo $sql;exit;
    $clientes = mysql_query($sql) or die("Error SQL2 ".  mysql_error());
    //echo $sql;exit;
    if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") ) {
        header("Content-type: application/xhtml+xml;charset=utf-8");
    } else {
        header("Content-type: text/xml;charset=utf-8");
    }
    $et = ">";
    echo "<?xml version='1.0' encoding='utf-8'?$et\n";
    echo "<rows>";
    echo "<page>".$page."</page>";
    echo "<total>".$total_pages."</total>";
    echo "<records>".$count."</records>"; // be sure to put text data in CDATA
    $granTotal = 0;
    while($row = mysql_fetch_array($clientes,MYSQL_ASSOC)) {
        echo "<row id='".$row['cliente']."'>";
        echo "<cell>". utf8_encode(htmlspecialchars($row['name']))."</cell>"; //htmlspecialchars()
        echo "<cell>". utf8_encode(htmlspecialchars($row['cliente']))."</cell>";
        
        //calcula valor facturado por cada cliente
        $sql = "SELECT interno,valoralquiler,fechafacturar
                    FROM `vfacturables`
                    WHERE (datediff('$fechaCorte',fechafacturar) >= $dias)
                    and cliente = '".$row['cliente']."'";
        $result = mysql_query($sql) or die("Error SQL3 ".  mysql_error());
        $facturarCliente = 0;
        while ($row2 = mysql_fetch_array($result)) {
            $fecEqu = $row2['fechafacturar'];
            $fecAct = $fechaCorte;
            if($fecAct > $fecEqu){
                $valorDia = $row2['valoralquiler'] / 30;
                $mesEqu = date('n', strtotime($fecEqu)) ;
                $diaEqu = date('j', strtotime($fecEqu)) + 1;    //Se comienza a facturar un dia despues
                $anoEqu = date('Y', strtotime($fecEqu)) ;
                $mesAct = date('n', strtotime($fecAct)) ;
                $diaAct = date('j', strtotime($fecAct)) ;
                $anoAct = date('Y', strtotime($fecAct)) ;
                $pago = 0;
                while($anoEqu < $anoAct || $mesEqu < $mesAct ){ //|| $diaAct != $diaEqu
                    $fecha = mktime( 0, 0, 0, $mesEqu, 1, $anoEqu );
                    if (!(date("t",$fecha) == $diaEqu)){
                        $dia = 30 -$diaEqu;
                        if($dia > 0){
                            $pago = $pago + $dia * $valorDia;
                        }
                    }
                    else{
                        $pago = $pago + $valorDia;
                    }
                    $diaEqu = 0;
                    if($mesEqu == 12){
                        $anoEqu ++;
                        $mesEqu = 1;
                    }
                    else
                        $mesEqu ++;
                }
                $fecha = mktime( 0, 0, 0, $mesAct, 1, $anoAct );
                $dia = (date("t",$fecha) == $diaAct)?30:$diaAct;
                $pago = $pago + $dia * $valorDia;
                $facturarCliente =  $facturarCliente + $pago;
            }
            $granTotal = $granTotal + $facturarCliente;
            
        } //fin while ($row2 = mysql_fetch_array($result)) {
        echo "<cell>".$facturarCliente."</cell>";
        echo "</row>";
    }
    echo "<userdata name='tvalor'>".money_format('%n',$granTotal)."</userdata>";
    echo "</rows>";
    
function Strip($value)
{
	if(get_magic_quotes_gpc() != 0)
  	{
    	if(is_array($value))
			if ( array_is_associative($value) )
			{
				foreach( $value as $k=>$v)
					$tmp_val[$k] = stripslashes($v);
				$value = $tmp_val;
			}
			else
				for($j = 0; $j < sizeof($value); $j++)
        			$value[$j] = stripslashes($value[$j]);
		else
			$value = stripslashes($value);
	}
	return $value;
}
function array_is_associative ($array)
{
    if ( is_array($array) && ! empty($array) )
    {
        for ( $iterator = count($array) - 1; $iterator; $iterator-- )
        {
            if ( ! array_key_exists($iterator, $array) ) { return true; }
        }
        return ! array_key_exists(0, $array);
    }
    return false;
}
?>